古都の治安を守れい！これがAlteryx、Snowflake、Lookerで作る奈良県犯罪MAPじゃあアァァッッ！
悪が蔓延るこの地にて 起きる犯罪はEverywhere
己を守る手段とは データの可視化に他ならず
古都の平和を守るため 分析ツールをまといて即、参上！
大阪オフィス 玉井励
やったこと
下記のダッシュボードを作成したでござる！
ドリルダウンして各犯罪の詳細を出すことも可能じゃあ！
環境
使用ツール
- Alteryx Designer
- 2020.2
- Snowflake
- 4.33.2
- Looker
- 7.16.26
使用データ
- 下記のCSVデータ2年分（2018〜2019）
- ひったくり
- 車上ねらい
- 部品ねらい
- 自動販売機ねらい
- 自動車盗
- オートバイ盗
- 自転車盗
その他
今回作成したLookMLは下記にアップしているので、全コードが見たいという方はこちらをご覧あれ。
手順
概要
下記の通りに作業するぞい
- csvデータをAlteryxで整形する
- Snowflakeにロードする
- Lookerで上記データをモデリングする
- Lookerで可視化する
作業をする前に、どういう形でデータをモデリングするか考える
実際に作業に入る前に、大体のデータの持ち方について考えるぞい
今回のデータは、犯罪手口別にCSVファイルが分かれておる。で、こちらのデータ、犯罪手口によって、カラム名が多少異なっておる。
犯罪が起こった場所に関する情報は、どのファイルでも共通して存在しておるが、「現金以外の主な被害品」みたいな情報は、犯罪手口によってあったりなかったりするのう。この他にも、犯罪手口によって固有の情報（カラム）がある。つまり、全てをUNIONして1つのテーブルにすることはできないということじゃなあ（無理やりやってもいいが、NULLが大量発生するので非常に面倒）。
だから、ひとまず2018年と2019年に分かれているのは併合するとして、犯罪手口別に分かれている件については、そのままテーブルを分けて入れるぞい。
そして、共通のカラムと別のカラムが混在しているようなデータの場合、Lookerであればextendsを使って、無駄な記述を増やすことなくモデリングすることができるので、その方向性で参ろうか。
Alteryxでデータを整形してSnowflakeにロードする
Alteryxのワークフロー
まずはAlteryx Designerでデータを整形しつつSnowflakeにロードじゃあ。今回作成したワークフローは下記の通りじゃ。
このワークフローで主にやっているのは下記の通り。
- 2年分のファイルを一括で読み込む（事実上のUNION）
- 面倒じゃが、今回は犯罪手口別にワークフローを作成して実行したぞい
- カラム名を英字に修正
- Snowflake自体は日本語カラムいけるのじゃが、Lookerで扱うときに面倒なことになるので修正
- 住所から緯度経度を取得（ジオコーディング）
- 「発生時期」というカラムの修正
- 「あるある」ですが、データによって時間表記の仕方がバラバラだったので修正
- データがまともに入っていないものは削除じゃあ！
- 「事件があった」以外のデータが全く入っていないレコードが数件あったので、今回はズババババッサリ削除。
Snowflakeを確認する
ちゃんとロードできていることを確認するでござる。
Lookerでデータをモデリングする
さあ、今回のメインであるデータモデリングじゃな。
共通viewをつくる
当初の計画通り、まずはextendsを使用して各テーブルに共通して存在するカラム（発生場所の住所とか管轄警察署とか）を1回の記述で済ませるぞい。というわけで、まずはextend_base.viewを作成し、共通カラムを定義するぞい。このview自体はそのまま使わず、継承して別のviewファイルを作成していくので、extension: requiredを記述しておくぞい。
犯罪全般用のviewをつくる
ここからは「どのような分析をしたいか」という目的によって作り方は変わってくるぞい。私はまず手口関係なく「奈良県の犯罪全体に関することを分析したい」と思ったので、全部の犯罪手口を横串で分析できるデータモデルを作ることにしたぞい。
作り方じゃが、犯罪手口固有のカラムはいらないので、基本的には先程の共通viewでいけるはずじゃ。ただし、DWH（Snowflake側）はテーブルが手口毎にできているので、全てUNIONする必要があるぞい。Snowflake側でやってもええが、せっかくなので、Lookerの派生テーブルで定義するぞい（Looker上でUNIONしたい場合は、SQL派生テーブルを定義するしかないのじゃよ）。
犯罪手口個別のviewファイルをつくる
この時点で「犯罪全体のダッシュボードのどれかを選ぶと、その犯罪手口個別のダッシュボードに飛ぶ」という構想が頭の中に出来ておった。というわけで、そのダッシュボードを作るための、犯罪手口個別のviewをつくるぞい。
下記は「ひったくり」のviewじゃ。
犯罪の発生場所などは、extend_base側で書いてあるものを継承しているので、再度記述する必要はないぞい。ひったくりテーブルだけに存在する3つのカラムについて定義するだけでOKじゃ。
これを犯罪手口テーブルの数だけ作成するぞい。
exploreは何もやらない
今回は各view（テーブル）がそれぞれ独立している（リレーショナルがない）ため、exploreは質素じゃあ…。joinは無いぞい。
Lookerでダッシュボードをつくる
必要なデータモデリングが一通り終わったので、可視化に勤しむとするかのう。記事冒頭に乗せているダッシュボードを作るんじゃが、ほとんどは普通の棒グラフなので、ここではMAPと別ダッシュボードへの遷移について解説するぞい。
地図について
Lookerは緯度経度のデータがあれば、それを自動的に地図にプロットしてくれるぞい（そのためにAlteryxで事前ジオコーディングした）。緯度経度のデータは、LookMLで下記のように定義するんじゃ。
これをExploreで可視化してみるぞい。位置情報と、それに合わせて可視化したいmeasureを選ぶのじゃ。
すると、下記のようにmeasureの大小を地図上にプロットしてくれるぞい。
プロットの描画方法は色々選べるぞい。今回はヒートマップを採用じゃあ！。やはり人口の多い市あたりが犯罪が多いのう！
別ダッシュボードへの遷移
冒頭のダッシュボード紹介にあるように、このダッシュボードは、地図のある部分を選択→ドリルダウンが出てくる→犯罪手口を選ぶとそこから犯罪手口固有ダッシュボードに遷移…という流れが仕掛けられておる。
これを実現するためには、先程出たbasic_info.viewにあるdimentionにあるパラメータを追加じゃ。
Lquidで、選択した犯罪手口に応じて飛ばすダッシュボードを変えるようにしておるんじゃな。
せっかくなので色々見てみる
自分が住んでいる場所の犯罪についてパトロール（？）してみるぞい。
葛城市あたりを見てみるぞい。さすが葛城市、犯罪がめちゃくちゃ少ないのう。
詳しく見てみるぞい。やはり自転車はよく盗まれるようじゃな…。1件だけ車の部品がやられている犯罪があるようじゃ。
葛城市の東室という地域に絞った自転車盗ダッシュボードじゃ。意外と若い人がやられておる。あと、鍵はちゃんとしたほうがよさそうじゃな。
やはり駐輪場でやられることが多いようじゃ。出かける時は注意じゃぞ。
おわりに
公式が既に犯罪マップを提供していたというオチじゃあ！！！
